
// =================================================================== 
// class description : 
// =================================================================== 
// File: UserRoleDBHelper.cs
// Copyright  @ AC
// History:
// 		2012/7/25  created by 
// 
// =================================================================== 

using System;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using InternalManagementSystem.DAO.Models;

namespace InternalManagementSystem.DAO
{
    public class UserRoleDAO : DataAccessObject
    {	
		/// <summary>
    	///Description   :gets a UserRole record from db
    	///				  package , should be implicly downcast
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : UserRole object. Will be null if record not found.
    	/// </summary>
		public UserRole LoadUserRole(System.Int32 id)
		{
			string sqlSelect = "SELECT  ID,  Name  FROM UserRole WHERE  ID = @ID ";
			SqlConnection conn = new SqlConnection(ConnectionString);
			UserRole pUserRole = null;
		
			//SqlCommand sqlCmd = new SqlCommand("usSelectUserRole");
			SqlCommand sqlCmd = new SqlCommand(sqlSelect);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
		 
			sqlCmd.Parameters.Add("@ID",SqlDbType.Int).Value =id;
			try
			{
				// open the connection and create the DataReader
				conn.Open();
				SqlDataReader dr = sqlCmd.ExecuteReader();
		
				// output the results and close the connection. Should be <=1 results.
				if(dr.Read())
				{
            		pUserRole = new UserRole();
					pUserRole.ID = (dr["ID"]==DBNull.Value)?0:Convert.ToInt32(dr["ID"]);        			
					pUserRole.Name = (dr["Name"]==DBNull.Value)?string.Empty:Convert.ToString(dr["Name"]);        			
				}
		
				conn.Close();
		
			}
			catch (SqlException pEx)
			{
				conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
				throw pEx;
			}
		
			return pUserRole;			
		}
	
		/// <summary>
    	///Description   :gets a UserRole collection object of  all
    	///				  UserRole records in the db
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : UserRole collection object.
    	/// </summary>
		public UserRoleCollection LoadAllUserRoles()
		{
			string sqlSelect = "SELECT  ID,  Name  FROM UserRole";
			SqlConnection conn = new SqlConnection(ConnectionString);
			UserRoleCollection pUserRoleCollection =new UserRoleCollection();
		
			//SqlCommand sqlCmd = new SqlCommand("usSelectUserRoleAll");
			SqlCommand sqlCmd = new SqlCommand(sqlSelect);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
			
			try
			{
				// open the connection and create the DataReader
				conn.Open();
				SqlDataReader dr = sqlCmd.ExecuteReader();
		
				// output the results and add to collection and close the connection.
				while(dr.Read())
				{	
					UserRole vo = new UserRole();
					vo.ID = (dr["ID"]==DBNull.Value)?0:Convert.ToInt32(dr["ID"]);        			
					vo.Name = (dr["Name"]==DBNull.Value)?string.Empty:Convert.ToString(dr["Name"]);        			
					
            		pUserRoleCollection.Add(vo);
				}
		
				conn.Close();
			}
			catch (SqlException pEx)
			{
				conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
				throw pEx;
			}
		
			return pUserRoleCollection;		
		}
		
		/// <summary>
    	///Description   :gets a UserRole collection object of  all
    	///				  UserRole records in the db
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : UserRole collection object.
    	/// </summary>
		public UserRoleCollection LoadAllUserRoles(string filter, string sort)
		{
			string sqlSelect = "SELECT  ID,  Name  FROM UserRole ";
			if (!string.IsNullOrEmpty(filter))
			{
				sqlSelect += " WHERE " + filter;
			}
			if (!string.IsNullOrEmpty(sort))
			{
				sqlSelect += " order by " + sort;
			}
			SqlConnection conn = new SqlConnection(ConnectionString);
			UserRoleCollection pUserRoleCollection =new UserRoleCollection();
		
			//SqlCommand sqlCmd = new SqlCommand("usSelectUserRoleDynamic");
			SqlCommand sqlCmd = new SqlCommand(sqlSelect);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
			
			//sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
			//sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;
			
			try
			{
				// open the connection and create the DataReader
				conn.Open();
				SqlDataReader dr = sqlCmd.ExecuteReader();
		
				// output the results and add to collection and close the connection.
				while(dr.Read())
				{	
					UserRole vo = new UserRole();
					vo.ID = (dr["ID"]==DBNull.Value)?0:Convert.ToInt32(dr["ID"]);        			
					vo.Name = (dr["Name"]==DBNull.Value)?string.Empty:Convert.ToString(dr["Name"]);        			
					
            		pUserRoleCollection.Add(vo);
				}
		
				conn.Close();
			}
			catch (SqlException pEx)
			{
				conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
				throw pEx;
			}
		
			return pUserRoleCollection;		
		}
		
		/// <summary>
    	///Description   :gets a DataSet collection object of  all
    	///				  UserRole records in the db
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : DataSet object.
    	/// </summary>
		public DataSet GetDataSet()
		{
			DataSet ds = null;
			SqlDataAdapter xAdapter = null;
		
			string sqlSelect = "SELECT  ID,  Name  FROM UserRole";
			SqlConnection conn = new SqlConnection(ConnectionString);
		
			//SqlCommand sqlCmd = new SqlCommand("usSelectUserRoleAll");
			SqlCommand sqlCmd = new SqlCommand(sqlSelect);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
			
			try
			{
				conn.Open();
		
				xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
			}
			catch (SqlException pEx)
			{
				conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
				throw pEx;
			}
		
			return ds;		
		}
		
		/// <summary>
    	///Description   :gets a DataSet collection object of  all
    	///				  UserRole records in the db
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : DataSet object.
    	/// </summary>
		public DataSet GetDataSet(string filter, string sort)
		{
			DataSet ds = null;
			SqlDataAdapter xAdapter = null;
		
			string sqlSelect = "SELECT  ID,  Name  FROM UserRole ";
			if (!string.IsNullOrEmpty(filter))
			{
				sqlSelect += " WHERE " + filter;
			}
			if (!string.IsNullOrEmpty(sort))
			{
				sqlSelect += " order by " + sort;
			}
			SqlConnection conn = new SqlConnection(ConnectionString);
		
			//SqlCommand sqlCmd = new SqlCommand("usSelectUserRoleDynamic");
			SqlCommand sqlCmd = new SqlCommand(sqlSelect);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
			
			//sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
			//sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;
			
			try
			{
				conn.Open();
		
				xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
			}
			catch (SqlException pEx)
			{
				conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
				throw pEx;
			}
		
			return ds;		
		}
	
		/// <summary>
    	///Description   :updates a UserRole record in the db
    	///Pre-condition : none
    	///Post-condition: none
    	///Return        : true - if record was update .
		//				   false - if record was not updated;
    	/// </summary>
		public void UpdateUserRole(UserRole pUserRole)
		{
			string sqlUpdate = "UPDATE UserRole SET  Name = @Name WHERE ID = @ID  ";
			SqlConnection conn = new SqlConnection(ConnectionString);
			
	    	// create the command for the stored procedure
	    	//SqlCommand sqlCmd = new SqlCommand("usInsertUpdateUserRole");
			SqlCommand sqlCmd = new SqlCommand(sqlUpdate);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
	
			sqlCmd.Parameters.Add("@ID",SqlDbType.Int).Value = pUserRole.ID;
			sqlCmd.Parameters.Add("@Name",SqlDbType.VarChar).Value = pUserRole.Name;
	    
	    	try
	    	{
	        	// open the connection
	        	conn.Open();
			
				//execute the update command
	        	sqlCmd.ExecuteNonQuery();
			
				//close the connection
	        	conn.Close();
	    	}
	    	catch (SqlException pEx)
	    	{
	        	conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
	        	throw pEx;
	    	}
		}

		/// <summary>
		///Description   : create a new UserRole record in the db
		///Pre-condition : none
		///Post-condition: none
		///Return        : None.
		/// </summary>
		public void InsertUserRole(UserRole pUserRole)
		{
			string sqlInsert = " INSERT INTO UserRole (Name) VALUES (   @Name) ";
			SqlConnection conn = new SqlConnection(ConnectionString);
			
	    	// create the command for the stored procedure
	    	//SqlCommand sqlCmd = new SqlCommand("usInsertUserRole");
			SqlCommand sqlCmd = new SqlCommand(sqlInsert);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;
		
			
			sqlCmd.Parameters.Add("@Name",SqlDbType.VarChar).Value = pUserRole.Name;
			
	    	try
	    	{
	        	// open the connection and run the insert command
	        	conn.Open();
	        	sqlCmd.ExecuteNonQuery();

	        	conn.Close();
	    	}
	    	catch (SqlException pEx)
	    	{
	        	conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
	        	throw pEx;
	    	}
		}
	
		/// <summary>
    	///Description   : Deletes a UserRole record from the db
    	///Pre-condition : record has to exist in db
    	///Post-condition: none
    	///Return        : true - if record was deleted successfully.
    	///				   false - if record was not deleted;
    	/// </summary>
    	public bool DeleteUserRole(int pUserRoleID)
    	{
			string sqlDelete = " delete from UserRole WHERE ID = @ID  ";
			SqlConnection conn = new SqlConnection(ConnectionString);
        	bool pboolResult = false;

        	// create the command for the stored procedure
        	//SqlCommand sqlCmd = new SqlCommand("usDeleteUserRole");
			SqlCommand sqlCmd = new SqlCommand(sqlDelete);
			sqlCmd.Connection = conn; 
			//sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@ID", SqlDbType.Int).Value = pUserRoleID;
    
        	try
        	{
            	// open the connection and create the DataReader
            	conn.Open();
            	sqlCmd.ExecuteNonQuery();

            	conn.Close();
            	pboolResult = true;
        	}
        	catch (SqlException pEx)
        	{
            	conn.Close();
				LastErrorMessage = pEx.Message;
				LogErrorMessage();
            	throw pEx;
        	}
        	return pboolResult;
    	}
	}
}